<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>Untitled Document</title>
</head>

<body><pre>
<?php
include $_SERVER['DOCUMENT_ROOT'].'/Classes/PHPExcel/IOFactory.php';

//	Read Excel Directory//
$dirExcelFiles="excel/";
$dirRenamedExcelFiles="renamed_excels/";	
$aExcelFiles=[];
if ($handle = opendir($dirExcelFiles)) {
while (false !== ($entry = readdir($handle))) {
	if ($entry != "." && $entry != "..") {
		$sFileName=substr($entry,0,10);
		$sFileType=pathinfo($entry,PATHINFO_EXTENSION);
		$sNewFileName=$dirExcelFiles.$sFileName.".".$sFileType;
		rename($dirExcelFiles.$entry,$sNewFileName);
		$aExcelFiles[$sFileName]=$sNewFileName;
	}
}
closedir($handle);
}
//print_r($aExcelFiles);
// End Read directory



include "db.conf.php";

$sTableName="2014_w_la";
$sTeamWork="tw_1";

$sqlConnect = new mysqli($sServer,$sUser,$sPassword,$sDatabaseName);
$sqlConnect->query("SET NAMES 'UTF8'");

if ($sqlConnect ->connect_error) {
	die("Can't connect to database");
}

//	select teams
for ($nTeam=1;$nTeam<20;$nTeam++)
{	
	echo"<h1>".$nTeam."</h1>";
	//Reset array of students
	$aStudents=[];
	
	//	Get the students in $nTeam
	$sQuery = "SELECT `student_number`,`name`,`".$sTeamWork."` FROM `".$sTableName."` WHERE `team`=".$nTeam;

	$sqlResult=$sqlConnect->query($sQuery);
	//	Get number of students in Teams
	$nTeamSize = $sqlResult->num_rows;
	
	while ($row = $sqlResult->fetch_array(MYSQLI_ASSOC))   
	{
		//$sStudentNumber=$row["student_number"];
		$aStudents[$row["name"]]=["student_number"=>$row["student_number"],"score"=>$row[$sTeamWork],"peer_eval"=>0,"submitted"=>0];
	}
	//print_r($aStudents);
	//	End getting the students in $nTeam
	
	//	Scan through students in $nTeam
	foreach ($aStudents as $sStudentName => $aStudentInfo)
	{	
	    //Reset total distribution
	    $nTotalEvaluation=0;
	    
		//	Evaluation file name
		$sFileName=$aStudentInfo["student_number"];
		
		// Student name
		//$sStudentName=$aStudents[$sStudentNumber];
				
		//	If student $sStudentNumber submit file name.
		if (array_key_exists($sFileName,$aExcelFiles))
		{
		    //  student submited peer evaluation
		    $aStudents[$sStudentName]["submitted"]=1;
			$inputFileName=$aExcelFiles[$sFileName];
			echo $inputFileName."<br/>";
			//  Read your Excel workbook
			try 
			{
				$inputFileType = PHPExcel_IOFactory::identify($inputFileName);
				$objReader = PHPExcel_IOFactory::createReader($inputFileType);
				$objPHPExcel = $objReader->load($inputFileName);
			} 
			catch(Exception $e) 
			{
				die('Error loading file "'.pathinfo($inputFileName,PATHINFO_BASENAME).'": '.$e->getMessage());
			}
			
			//  Get worksheet dimensions
			$sheet = $objPHPExcel->getSheet(0);
			$highestRow = $sheet->getHighestRow();
			$highestColumn = $sheet->getHighestColumn();
			
			//  Loop through each row of the worksheet in turn
			//  Read rows of data into an array
			$excelData = $sheet->rangeToArray('A2:B'.($nTeamSize+1) ,
					NULL,
					TRUE,
					FALSE);

			for ($row=0;$row<$nTeamSize;$row++)
			{
			    //   Remove space if exist
    		    //$excelData[$row][0]= preg_replace('/\s+/', '', $excelData[$row][0]);
				 $excelData[$row][0] =str_replace(" ","",$excelData[$row][0]);
			    //   update total evaluation points
    		    $nTotalEvaluation +=$excelData[$row][1];
			    echo ($row." ".$excelData[$row][0]." ".$excelData[$row][1]."</br>");
			}
			
			for ($row=0;$row<$nTeamSize;$row++)
			{
			    //   update students' peer eval
			    if (array_key_exists($excelData[$row][0],$aStudents))
	            {
	                $aStudents[$excelData[$row][0]]["peer_eval"]+=($excelData[$row][1]/$nTotalEvaluation);
	            }
	            else
	            {
	                echo "students name <strong>".$excelData[$row][0]."</strong> not exists\n";
	            }
			}
			//  update student's peer eval
			//  $aStudents[$excelData[0][0]]+=($excelData[0][1]/$nTotalEvaluation);
			
			//print_r($rowData);echo "</br>";
			//  Insert row data array into your database of choice here
			echo "<font color=\"red\"><strong>Total: ".$nTotalEvaluation."</strong></font></br>";
		}
	}
	print_r($aStudents);
}
?></pre>
</body>
</html>
